package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Attribute;
import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.entities.Site;
import com.netcracker.training.automonic.dao.entities.Statistics;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see Object
 */

public class OracleObject implements Object // TODO Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static Object createObject(Site site, String name)
	{
		Object object = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT seq_Objects.NEXTVAL FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				int id = rs.getInt(1);
				object = new OracleObject(id, site, name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(object);
		}
		
		return object;
	}
	
	public static Object selectObjectById(int id)
	{
		Object object = null;
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_OBJECT_BY_ID, id);
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				Site site = OracleSite.selectSiteById(rs.getInt("site"));
				object = new OracleObject(rs.getInt("object_id"),
				                          site,
				                          rs.getString("name"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(object);
		}
		
		return object;
	}
	
	public static Object selectObjectBySiteAndName(Site site, String name)
	{
		Object object = null;
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_OBJECT_BY_SITE_NAME,
			                           site.getId(), name);
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				object = new OracleObject(rs.getInt("object_id"),
				                          site, name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(object);
		}
		
		return object;
	}
	
	public static List<Object> selectObjectsBySite(Site site)
	{
		List<Object> objects = new LinkedList<Object>();
		
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_OBJECT_BY_ID, site.getId());
			
			ResultSet rs = st.executeQuery(sql);
			
			while (rs.next())
			{
				Object object = new OracleObject(rs.getInt("object_id"),
				                                 site,
				                                 rs.getString("name"));
				objects.add(object);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Object object : objects)
					LOGGER.trace(object);
		}
		
		return objects;
	}
	
	public static List<Object> selectObjectsByName(String name)
	{
		List<Object> objects = new LinkedList<Object>();
		
		try (Statement st = con.createStatement())
		{
			String sql = String.format(SELECT_OBJECTS_BY_NAME, name);
			
			ResultSet rs = st.executeQuery(sql);
			
			while (rs.next())
			{
				Site site = OracleSite.selectSiteById(rs.getInt("site"));
				Object object = new OracleObject(rs.getInt("object_id"),
				                                 site,
				                                 rs.getString("name"));
				objects.add(object);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Object object : objects)
					LOGGER.trace(object);
		}
		
		return objects;
	}
	
	public static List<Object> selectAllObjects()
	{
		List<Object> objects = new LinkedList<Object>();
		
		try (Statement stmt = con.createStatement())
		{
			ResultSet rs = stmt.executeQuery(SELECT_ALL_OBJECTS);
			
			while (rs.next())
			{
				Site site = OracleSite.selectSiteById(rs.getInt("site"));
				Object object = new OracleObject(rs.getInt("object_id"),
				                                 site,
				                                 rs.getString("name"));
				objects.add(object);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Object object : objects)
					LOGGER.trace(object);
		}
		
		return objects;
	}
	
	private final int id;
	private final Site site;
	private String name = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleObject(int id, Site site, String name)
	{
		this.id   = id;
		this.site = site;
		setName(name);
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public int getId()
	{
		return id;
	}

	@Override
	public Site getSite()
	{
		return site;
	}

	@Override
	public String getName()
	{
		return name;
	}

	@Override
	public void setName(String name)
	{
		if (this.name.hashCode() != name.hashCode())
			modified = true;
		name = name.replaceAll("'{1,}", "''");
		this.name = name;
	}

	@Override
	public List<Attribute> getAttributes()
	{
		return OracleAttribute.selectAttributesByObject(this);
	}

	@Override
	public List<Statistics> getStatistics()
	{
		return OracleStatistics.selectStatisticsByObject(this);
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		return String.format("%d [%s] %s", id, site, name);
	}
	
	@Override
	public void save()
	{
		boolean objectExists = (selectObjectById(id) != null);
		
		if (objectExists)
		{
			if (modified)
			{
				String sql = String.format(
						"UPDATE Objects "
					  + "SET name = '%s' "
					  + "WHERE object_id = %d",
						name, id);
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			String sql = String.format(
					"INSERT INTO Objects "
				  + "	VALUES (%d, %d, '%s')",
					id, site.getId(), name);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved object: " + this);
	}

}
